package common.dao.mysql;

/**
 * Created by Common on 11/24/16.
 */




import common.helper.toolkit.HttpToolkit;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;

/*
https://dev.mysql.com/doc/connector-j/5.1/en/
http://www.cnblogs.com/aniuer/archive/2012/09/10/2679241.html
 */

public class MySqlDao {
    final Logger logger = LoggerFactory.getLogger(MySqlDao.class);

    private final String config_path = "/mysql_admin/mysql_servers.yaml";

    private Connection conn;

    public MySqlDao(){
        //HttpToolkit.HostPortPair pair = HttpToolkit.getHostPort(this.config_path);
        MySqlConf.ConfInfo info = MySqlConf.getMySqlConf(this.config_path);
        logger.info("[MySqlDao] host = {}, port = {}, user = {}, pswd = {} ", info.host,info.port,info.user, info.pswd);
        init(info.host, info.port, info.user, info.pswd);

    }

    public MySqlDao(String host, int port, String user, String pswd ){
        init(host,port,user,pswd);
    }

    protected void init(String host, int port, String user, String pswd){
        try{
            //调用Class.forName()方法加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //System.out.println("成功加载MySQL驱动！");
            logger.info("[MySqlDao] load jdbc driver success!");
        }catch(ClassNotFoundException e){
            //System.out.println("找不到MySQL驱动!");
            logger.error("[MySqlDao] can't find mysql driver!");
            e.printStackTrace();
        }

        String url = "jdbc:mysql://" + host + ":" +  port + "/mysql";
        logger.info("[MySqlDao] url = {}", url);
        //String url="jdbc:mysql://192.168.1.107:3306/mysql";    //JDBC的URL
        //调用DriverManager对象的getConnection()方法，获得一个Connection对象
        try {
            this.conn = DriverManager.getConnection(url,    "root","pkulky201");
            //创建一个Statement对象
            //Statement stmt = conn.createStatement(); //创建Statement对象
            //System.out.print("成功连接到数据库！");
            //stmt.close();
            //conn.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    public void closeCon(){
        try{
            this.conn.close();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }

    @Override
    protected void finalize(){
        try {
            super.finalize();
        }
        catch (Throwable e){
            e.printStackTrace();
        }
        closeCon();
    }


    public Connection getCon(){
        return this.conn;
    }

    public Statement getStatement(){
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
        return stmt;
    }

    public void closeStatement( Statement stmt ){
        try {
            stmt.close();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }


    /*
    *****************************************************************************
    MAIN
     */
    public static void main(String[] args){
        // 1)
        //test_select();
        // 2)

        //test_createtb("kg_couchdb_status");
        // 3)
        //test_insert();
        // 4)
        //test_update();
        MySqlDao dao = new MySqlDao();




    }

    public static void test_select(int table_type){
        MySqlDao dao = new MySqlDao("162.105.16.229", 3306, "root", "pkulky201");
        Statement stmt = dao.getStatement();
        String sql = "select * from tps.vendor limit 5;";
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(sql);//创建数据对象
            while (rs.next()){
                System.out.println(rs.getInt(1));
            }
            rs.close();
        }catch (SQLException e){
            e.printStackTrace();
        }

        dao.closeStatement(stmt);
    }

    public static void test_createdb(){
        MySqlDao dao = new MySqlDao("162.105.16.229", 3306, "root", "pkulky201");
        Connection conn = dao.getCon();
        Statement stmt = dao.getStatement();
        String sql = "create database media_search_engine;";
        try {
            stmt.execute(sql);
            //conn.
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }

    public static void test_createtb( String table){
        MySqlDao dao = new MySqlDao("162.105.16.229", 3306, "root", "pkulky201");
        Connection conn = dao.getCon();
        Statement stmt = dao.getStatement();
        String sql = "create TABLE IF NOT EXISTS media_search_engine." + table + " ";
        sql +=      "("
                +   "ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
                +   ", couchdb varchar(20)"
                +   ", id varchar(200)"
                +   ", serial bigint"
                +   ", status int"
                +   ");";
        try {
            System.out.println(sql);
            stmt.execute(sql);
            //conn.
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }

    public static void test_insert(){
        MySqlDao dao = new MySqlDao("162.105.16.229", 3306, "root", "pkulky201");
        Connection conn = dao.getCon();
        String sql = "insert into media_search_engine.doc_couchdb_status (couchdb,id,serial,status) values(\"program\", \"fsfebsvvwey6sa9sf23rvs99vsv\", 12, 1);";
        System.out.println(sql);
        try {
            Statement stmt = conn.createStatement();
            stmt.execute(sql);
            //conn.
            stmt.close();
            conn.close();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }

    public static void test_update(){
        MySqlDao dao = new MySqlDao("162.105.16.229", 3306, "root", "pkulky201");
        Connection conn = dao.getCon();
        String sql = "update  media_search_engine.doc_couchdb_status set status=0 where  serial=12;";
        System.out.println(sql);
        try {
            Statement stmt = conn.createStatement();
            stmt.execute(sql);
            //conn.
            stmt.close();
            conn.close();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
    }


    /*
    mysql -h162.105.16.229 -P3306 -uroot -ppkulky201
     */

}